﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
    public class AccountRoleProvider : PhotoGallery.Core.Providers.DALProvider.IAccountRoleProvider
    {
        #region Sql
        private static readonly string SqlSelectAccountRole = "SELECT [RoleId],[AccountId], [Status],[AddDate] FROM [AccountRole] WHERE  [RoleId]=@RoleId AND [AccountId]=@AccountId ;";

        private static readonly string SqlInsertAccountRole = "INSERT INTO [AccountRole]([RoleId],[AccountId], [Status]) values(@RoleId,@AccountId, @Status);";

        private static readonly string SqlUpdateAccountRole = "UPDATE [AccountRole] SET [Status] = @Status,[AddDate] = @AddDate WHERE  [RoleId]=@RoleId AND [AccountId]=@AccountId ;";

        private static readonly string SqlDeleteAccountRole = "DELETE [AccountRole] WHERE  [RoleId]=@RoleId AND [AccountId]=@AccountId ;";

        private static readonly string SqlGetAllAccountRole = "SELECT [RoleId],[AccountId], [Status],[AddDate] FROM [AccountRole];";



        private static readonly string SqlGetAllRoleByAccount = "select  [Role].[Id], [Role].[Status],[Role].[AddDate],[Role].[Name],[Role].[Description]  from role inner join accountrole on accountrole.roleid=role.id inner join account on account.id=accountrole.accountid and account.id=@accountid";

        #endregion

        #region Procedures
        /*

-- Begin of AccountRole
IF OBJECT_ID(N'[USP_AccountRole_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_AccountRole_Delete_By_Id]

IF OBJECT_ID(N'[USP_AccountRole_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_AccountRole_Select_By_Id]

IF OBJECT_ID(N'[USP_AccountRole_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_AccountRole_Insert]

IF OBJECT_ID(N'[USP_AccountRole_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_AccountRole_Update_By_Id]

IF OBJECT_ID(N'[USP_AccountRole_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_AccountRole_SelectAll]

GO

--region [USP_AccountRole_Delete_By_Id]
CREATE PROCEDURE [USP_AccountRole_Delete_By_Id]
	@RoleId	int,
	@AccountId	int
AS
SET NOCOUNT ON
DELETE [AccountRole] WHERE  [RoleId]=@RoleId AND [AccountId]=@AccountId ;
--endregion
GO

--region [USP_AccountRole_Select_By_Id]
CREATE PROCEDURE [USP_AccountRole_Select_By_Id]
	@RoleId	int,
	@AccountId	int
AS
SET NOCOUNT ON
SELECT [RoleId],[AccountId], [Status],[AddDate] FROM [AccountRole] WHERE  [RoleId]=@RoleId AND [AccountId]=@AccountId ;
--endregion
GO

--region [USP_AccountRole_Insert]
CREATE PROCEDURE [USP_AccountRole_Insert]
	@RoleId	int,
	@AccountId	int,
	@Status	int,
	@AddDate	datetime
AS
SET NOCOUNT ON
INSERT INTO [AccountRole]([RoleId],[AccountId], [Status],[AddDate]) values(@RoleId,@AccountId, @Status,@AddDate);
--endregion
GO

--region [USP_AccountRole_Update_By_Id]
CREATE PROCEDURE [USP_AccountRole_Update_By_Id]
	@Status	int,
	@AddDate	datetime,
	@RoleId	int,
	@AccountId	int
AS
SET NOCOUNT ON
UPDATE [AccountRole] SET [Status] = @Status,[AddDate] = @AddDate WHERE  [RoleId]=@RoleId AND [AccountId]=@AccountId ;
--endregion
GO

--region [USP_AccountRole_SelectAll]
CREATE PROCEDURE [USP_AccountRole_SelectAll]
AS
SET NOCOUNT ON
SELECT [RoleId],[AccountId], [Status],[AddDate] FROM [AccountRole];
--endregion
GO


-- End of AccountRole
*/
        #endregion

        #region IAccountRoleProvider Members
        public Core.Business.AccountRole Select(AccountRoleKey id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@RoleId", SqlDbType.Int, id.RoleId);
            sql.AddParameter("@AccountId", SqlDbType.Int, id.AccountId);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectAccountRole);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.AccountRole accountRole = new Core.Business.AccountRole();

                accountRole.Id = new Core.Business.AccountRoleKey();
                if (!reader.IsDBNull(0)) accountRole.Id.RoleId = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) accountRole.Id.AccountId = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) accountRole.Status = reader.GetInt32(2);
                if (!reader.IsDBNull(3)) accountRole.AddDate = reader.GetDateTime(3);

                reader.Close();
                return accountRole;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.AccountRole accountRole)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, accountRole.Status);
            //sql.AddParameter("@AddDate", SqlDbType.DateTime, accountRole.AddDate);
            sql.AddParameter("@RoleId", SqlDbType.Int, accountRole.Id.RoleId);
            sql.AddParameter("@AccountId", SqlDbType.Int, accountRole.Id.AccountId);
            sql.ExecuteSql(SqlInsertAccountRole);
        }

        public void Update(Core.Business.AccountRole accountRole)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, accountRole.Status);
            sql.AddParameter("@AddDate", SqlDbType.DateTime, accountRole.AddDate);
            sql.AddParameter("@RoleId", SqlDbType.Int, accountRole.Id.RoleId);
            sql.AddParameter("@AccountId", SqlDbType.Int, accountRole.Id.AccountId);
            sql.ExecuteSql(SqlUpdateAccountRole);
        }

        public void Delete(Core.Business.AccountRole accountRole)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@RoleId", SqlDbType.Int, accountRole.Id.RoleId);
            sql.AddParameter("@AccountId", SqlDbType.Int, accountRole.Id.AccountId);
            sql.ExecuteSql(SqlDeleteAccountRole);
        }

        public IList<Core.Business.AccountRole> GetAllAccountRole()
        {
            IList<Core.Business.AccountRole> accountRolelist = new List<Core.Business.AccountRole>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllAccountRole);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.AccountRole accountRole = new Core.Business.AccountRole();

                    accountRole.Id = new Core.Business.AccountRoleKey();
                    if (!reader.IsDBNull(0)) accountRole.Id.RoleId = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) accountRole.Id.AccountId = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) accountRole.Status = reader.GetInt32(2);
                    if (!reader.IsDBNull(3)) accountRole.AddDate = reader.GetDateTime(3);

                    accountRole.MarkOld();
                    accountRolelist.Add(accountRole);
                }
                reader.Close();
            }
            return accountRolelist;
        }
        #endregion





        public ICollection<Role> Find(Account account)
        {
            IList<Core.Business.Role> roles = new List<Core.Business.Role>();

            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@accountid", SqlDbType.Int, account.Id);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllRoleByAccount);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Role role = new Core.Business.Role();

                    if (!reader.IsDBNull(0)) role.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) role.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) role.AddDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) role.Name = reader.GetString(3);
                    if (!reader.IsDBNull(4)) role.Description = reader.GetString(4);


                    role.MarkOld();
                    roles.Add(role);
                }
                reader.Close();
            }
            return roles;
        }
    }
}
